﻿if object_id('fn_MapHTML_InspVisitDistributionPole') is not null
DROP FUNCTION fn_MapHTML_InspVisitDistributionPole

GO

CREATE FUNCTION [dbo].[fn_MapHTML_InspVisitDistributionPole]
(
	@InspectionVisitPoleId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html nvarchar(max)
	set @html = 
	(
		select 
			 'Distribution Inspection Visit' as 'TITLE'
			,Pole.*
			,maps.Name as 'MapName'
			,Pole.SectionFk as 'SectionName'
			,species.Name as 'SpecieName'
			,classes.Name as 'ClassName'
			,lengths.Name as 'LengthName'
			,treatments.Name as 'TreatMentName'
			,results.Vendor_Distribution_TypeFk
			,results.Vendor_Distribution_ResultTypeFk
			,resultTypes.Name as 'ResultTypeName'
			,inspectionType.Name as 'InspectionTypeName'
			,TLKV.LookUpText as 'KVName'
			,ACSR.LookUpText as 'ACSRName'
			,CT.LookUpText as 'ConductorTypeName'
			,CASE 
				when results.bElectrical = 1 then 'Yes'
				else 'No'
			 END as 'Electrical'
			,results.Comments

			-- Meter data
/*			,'Meter [' + CAST(meters.Vendor_Distribution_Meters_MeterId as nvarchar) + ']' as 'METERTITLE'
			,meters.Vendor_Distribution_Meters_MeterId as 'MeterId'
			,meters.Number as 'MeterNumber'
			,meters.Comments as 'MeterComments'*/

			--Distribution meters
			,(select 
					meters.*
					,replace(replace(replace(replace(Src.SettingValue, '{0}', meters.Vendor_Distribution_Meters_MeterId), '{1}', 'InspectionMeterDistribution'), '{2}', 85), '{3}', 85) as 'MeterImageSrc'
					--,replace(replace(replace(replace(Href.SettingValue, '{0}', meters.Vendor_Distribution_Meters_MeterId), '{1}', 'InspectionMeterDistribution'), '{2}', 640), '{3}', 480) as 'MeterImageH'
					,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', meters.Vendor_Distribution_Meters_MeterId), '{1}', 'InspectionMeterDistribution'), '{2}', 640), '{3}', 480)) as 'MeterImageHref'
					,'Meter [' + CAST(meters.Vendor_Distribution_Meters_MeterId as nvarchar) + ']' as 'METERTITLE'
					,(	select 
							MeterNotes.Name
						from 
							tb_Vendor_Distribution_NotesXMeter meterXNotes
						LEFT JOIN tb_Distribution_MeterNotes meterNotes
							ON meterNotes.MeterNoteId = meterXNotes.Vendor_Distribution_NoteFk
						WHERE 
							meterXNotes.Vendor_Distribution_MeterFk = meters.Vendor_Distribution_Meters_MeterId
						for xml path ('METERNOTES'), type) as '*'
			  from 
					tb_Vendor_Distribution_Poles as Pole
			  LEFT JOIN tb_Vendor_Distribution_Meters meters
					ON meters.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
			  where 
					meters.Vendor_Distribution_PoleFk = @InspectionVisitPoleId
			for xml path ('METER'), type) as '*'

			--Distribution transformers
			,(select 
					transformers.*
					,'Transformer [' + CAST(transformers.Vendor_Distribution_Transformers_TransformerId as nvarchar) + ']' as 'TRANSFORMERTITLE'
			  
					,CASE
						WHEN transformers.DualRated = 1 THEN 'Yes'
						ELSE 'No'
					 END 'DualRatedDesc'	
					,CASE
						WHEN transformers.Pre1978 = 1 THEN 'Yes'
						ELSE 'No'
					 END 'Pre1978Desc'
					,CASE
						WHEN transformers.Cutout = 1 THEN 'Yes'
						ELSE 'No'
					 END 'CutoutDesc'	
					,CASE
						WHEN transformers.Sticker = 1 THEN 'Yes'
						ELSE 'No'
					 END 'StickerDesc'	
					,TLP.LookUpText 'PhaseName'
					,TLC.LookUpText 'ClassName'
					,TLT.LookUpText 'TypeName'
					,TLTT.LookUpText 'TransformerName'
					,TLPCB.LookUpText 'PCBName'
					,TLA.LookUpText 'ArrestorDesc'
					,TLCUT.LookUpText 'CutoutValue'
					,TLCAN.LookUpText 'CanDesc'
					,TLDB.LookUpText 'DeterminedByDesc'

					--TransformerImages
					,(select
						 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Transformer_ImageId), '{1}', 'InspectionTransformerDistribution'), '{2}', 85), '{3}', 85) as '@src'
						,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Transformer_ImageId), '{1}', 'InspectionTransformerDistribution'), '{2}', 640), '{3}', 480)) as '@href'
						,Images.*
						from 
							tb_Vendor_Distribution_TransformerImages as Images
						where 
							Images.Vendor_Distribution_TransformerFk = transformers.Vendor_Distribution_Transformers_TransformerId
							for xml path ('TRANSIMAGES'), type) as '*'

					--TransformerNotes
					,(	select 
							TLK.LookUpText as 'LookUpText'
						from 
							tb_Vendor_Distribution_NotesXTransformer transXNotes
						LEFT JOIN tb_Vendor_Distribution_Lookups TLK
							ON TLK.Vendor_Distribution_LookupId = transXNotes.Vendor_Distribution_NoteFk
						WHERE 
							transXNotes.Vendor_Distribution_TransformerFk = transformers.Vendor_Distribution_Transformers_TransformerId
						for xml path ('TRANSNOTES'), type) as '*'
					
				from 
					tb_Vendor_Distribution_Poles as Pole
			  LEFT JOIN tb_Vendor_Distribution_Transformers transformers
					ON transformers.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
				LEFT JOIN tb_Vendor_Distribution_Lookups TLP
					ON TLP.Vendor_Distribution_LookupId = transformers.PhaseFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLC
					ON TLC.Vendor_Distribution_LookupId = transformers.ClassFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLT
					ON TLT.Vendor_Distribution_LookupId = transformers.TypeFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLTT
					ON TLTT.Vendor_Distribution_LookupId = transformers.TransformerTypeFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLPCB
					ON TLPCB.Vendor_Distribution_LookupId = transformers.PCBTypeFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLA
					ON TLA.Vendor_Distribution_LookupId = transformers.ArrestorKv
				LEFT JOIN tb_Vendor_Distribution_Lookups TLCUT
					ON TLCUT.Vendor_Distribution_LookupId = transformers.CutoutKv
				LEFT JOIN tb_Vendor_Distribution_Lookups TLCAN
					ON TLCAN.Vendor_Distribution_LookupId = transformers.CanFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLDB
					ON TLDB.Vendor_Distribution_LookupId = transformers.DeterminedBy
			  where 
					transformers.Vendor_Distribution_PoleFk = @InspectionVisitPoleId
			for xml path ('TRANSFORMER'), type) as '*'



			--Distribution sectionalizers
			,(select 
					sectionalizers.*
					,'Sectionalizer [' + CAST(sectionalizers.Vendor_Distribution_Sectionalizers_SectionalizerId as nvarchar) + ']' as 'SECTIONALIZERTITLE'
			  
					,CASE
						WHEN sectionalizers.Pre1978 = 1 THEN 'Yes'
						ELSE 'No'
					 END 'Pre1978Desc'

					,CASE
						WHEN sectionalizers.Sticker = 1 THEN 'Yes'
						ELSE 'No'
					 END 'StickerDesc'	
					,TLP.LookUpText 'PhaseName'
					,TLI.LookUpText 'InterrupterName'
					,TLPCB.LookUpText 'PCBName'
					,TLDB.LookUpText 'DeterminedByDesc'

					--SectionalizerImages
					,(select
						 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Sectionalizer_ImageId), '{1}', 'InspectionSectionalizerDistribution'), '{2}', 85), '{3}', 85) as '@src'
						,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Sectionalizer_ImageId), '{1}', 'InspectionSectionalizerDistribution'), '{2}', 640), '{3}', 480)) as '@href'
						,Images.*
						from 
							tb_Vendor_Distribution_SectionalizerImages as Images
						where 
							Images.Vendor_Distribution_SectionalizerFk = sectionalizers.Vendor_Distribution_Sectionalizers_SectionalizerId
							for xml path ('SECTIMAGES'), type) as '*'

					--SectionalizerNotes
					,(	select 
							TLK.LookUpText as 'LookUpText'
						from 
							tb_Vendor_Distribution_NotesXSectionalizer sectXNotes
						LEFT JOIN tb_Vendor_Distribution_Lookups TLK
							ON TLK.Vendor_Distribution_LookupId = sectXNotes.Vendor_Distribution_NoteFk
						WHERE 
							sectXNotes.Vendor_Distribution_SectionalizerFk = sectionalizers.Vendor_Distribution_Sectionalizers_SectionalizerId
						for xml path ('SECTNOTES'), type) as '*'
					
				from 
					tb_Vendor_Distribution_Poles as Pole
				LEFT JOIN tb_Vendor_Distribution_Sectionalizers sectionalizers
					ON sectionalizers.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
				LEFT JOIN tb_Vendor_Distribution_Lookups TLP
					ON TLP.Vendor_Distribution_LookupId = sectionalizers.PhaseFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLI
					ON TLI.Vendor_Distribution_LookupId = sectionalizers.Interrupter
				LEFT JOIN tb_Vendor_Distribution_Lookups TLPCB
					ON TLPCB.Vendor_Distribution_LookupId = sectionalizers.PCBType
				LEFT JOIN tb_Vendor_Distribution_Lookups TLDB
					ON TLDB.Vendor_Distribution_LookupId = sectionalizers.DeterminedByFk
			  where 
					sectionalizers.Vendor_Distribution_PoleFk = @InspectionVisitPoleId
			for xml path ('SECTIONALIZER'), type) as '*'



			--Distribution reclosers
			,(select 
					reclosers.*
					,'Reclosers [' + CAST(reclosers.Vendor_Distribution_Reclosers_RecloserId as nvarchar) + ']' as 'RECLOSERTITLE'
			  
					,CASE
						WHEN reclosers.Pre1978 = 1 THEN 'Yes'
						ELSE 'No'
					 END 'Pre1978Desc'

					,CASE
						WHEN reclosers.Sticker = 1 THEN 'Yes'
						ELSE 'No'
					 END 'StickerDesc'	
					,TLP.LookUpText 'PhaseName'
					,TLI.LookUpText 'InterrupterName'
					,TLPCB.LookUpText 'PCBName'
					,TLDB.LookUpText 'DeterminedByDesc'

					--SectionalizerImages
					,(select
						 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Recloser_ImageId), '{1}', 'InspectionRecloserDistribution'), '{2}', 85), '{3}', 85) as '@src'
						,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Distribution_Recloser_ImageId), '{1}', 'InspectionRecloserDistribution'), '{2}', 640), '{3}', 480)) as '@href'
						,Images.*
						from 
							tb_Vendor_Distribution_RecloserImages as Images
						where 
							Images.Vendor_Distribution_RecloserFk = reclosers.Vendor_Distribution_Reclosers_RecloserId
							for xml path ('RECLIMAGES'), type) as '*'

					--SectionalizerNotes
					,(	select 
							TLK.LookUpText as 'LookUpText'
						from 
							tb_Vendor_Distribution_NotesXRecloser reclXNotes
						LEFT JOIN tb_Vendor_Distribution_Lookups TLK
							ON TLK.Vendor_Distribution_LookupId = reclXNotes.Vendor_Distribution_NoteFk
						WHERE 
							reclXNotes.Vendor_Distribution_RecloserFk = reclosers.Vendor_Distribution_Reclosers_RecloserId
						for xml path ('RECLNOTES'), type) as '*'
					
				from 
					tb_Vendor_Distribution_Poles as Pole
				LEFT JOIN tb_Vendor_Distribution_Reclosers reclosers
					ON reclosers.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
				LEFT JOIN tb_Vendor_Distribution_Lookups TLP
					ON TLP.Vendor_Distribution_LookupId = reclosers.PhaseFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLI
					ON TLI.Vendor_Distribution_LookupId = reclosers.Interrupter
				LEFT JOIN tb_Vendor_Distribution_Lookups TLPCB
					ON TLPCB.Vendor_Distribution_LookupId = reclosers.PCBType
				LEFT JOIN tb_Vendor_Distribution_Lookups TLDB
					ON TLDB.Vendor_Distribution_LookupId = reclosers.DeterminedByFk
			  where 
					reclosers.Vendor_Distribution_PoleFk = @InspectionVisitPoleId
			for xml path ('RECLOSER'), type) as '*'


			--Distribution result notes
			,(select 
					Notes.*
			  from 
					tb_Vendor_Distribution_Results resultxNotes
			  inner join tb_Vendor_InspVisit_XmissionStructures_Notes Notes
					on resultxNotes.Vendor_Distribution_ResultTypeFk = notes.Vendor_InspVisit_XmissionStructures_NoteId
			  where 
					resultxNotes.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
			for xml path ('NOTE'), type) as '*'
				
			-- visit images <IMAGE href="" src="" />
			,(select
				PTT.Name
				FROM tb_Distributions_TopTypes PTT
				INNER JOIN tb_Vendor_Distribution_TopsXPole VDT
					ON VDT.Vendor_Distribution_TopFk = PTT.Distributions_TopTypeId
				WHERE VDT.vendor_distribution_polefk = Pole.Vendor_Distribution_Poles_PoleId
				for xml path ('TOPTYPES'), type) as '*'
			,(select
				 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 85), '{3}', 85) as '@src'
				,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480)) as '@href'
				--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
				,Images.*
				from 
					tb_Vendor_Distribution_Images as Images
				where 
					Images.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
					for xml path ('IMAGE'), type) as '*'

			-- Meter Notes
			/*,( select 
					MeterNotes.Name
				from 
					tb_Vendor_Distribution_NotesXMeter meterXNotes
				LEFT JOIN tb_Distribution_MeterNotes meterNotes
					ON meterNotes.MeterNoteId = meterXNotes.Vendor_Distribution_NoteFk
				WHERE 
					meterXNotes.Vendor_Distribution_MeterFk = meters.Vendor_Distribution_Meters_MeterId
				for xml path ('METERNOTES'), type) as '*'*/
			from 
				tb_Vendor_Distribution_Poles as Pole
				LEFT JOIN tb_Settings as Href 
					ON Href.SettingId = 25
				LEFT JOIN tb_Settings as Src 
					ON Src.SettingId = 24
				LEFT JOIN tb_Utility_Polygons maps
					ON Pole.MapFk = maps.Utility_PolygonId 
				LEFT JOIN tb_Utility_Polygons sections
					ON Pole.SectionFk = sections.Utility_PolygonId 
				LEFT JOIN tb_XMissionStructures_PoleSpecies species
					ON Pole.SpecieFk = species.XMissionStructures_PoleSpeciesId 
				LEFT JOIN tb_Distribution_Classes classes
					ON Pole.ClassFk = classes.ClassId 
				LEFT JOIN tb_Distribution_Lengths lengths
					ON Pole.LengthFk = lengths.LengthId
				LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
					ON Pole.TreatmentFk = treatments.XMissionStructures_PoleTreatmentId 				
				LEFT JOIN tb_Vendor_Distribution_InspectionResults results
					ON Pole.Vendor_Distribution_Poles_PoleId = results.Vendor_Distribution_PoleFk
				LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes resultTypes
					ON resultTypes.Vendor_InspVisit_XmissionStructures_ResultTypeId = results.Vendor_Distribution_ResultTypeFk
				LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type inspectionType
					ON inspectionType.Vendor_InspVisit_XmissionStructures_Inspection_TypeId = results.Vendor_Distribution_TypeFk
				LEFT JOIN tb_Vendor_Distribution_Lookups TLKV
					ON TLKV.Vendor_Distribution_LookupId = Pole.Kv
				LEFT JOIN tb_Vendor_Distribution_Lookups ACSR
					ON ACSR.Vendor_Distribution_LookupId = Pole.ACSR
				LEFT JOIN tb_Vendor_Distribution_Lookups CT
					ON CT.Vendor_Distribution_LookupId = Pole.ConductorType
			where
				Pole.Vendor_Distribution_Poles_PoleId = @InspectionVisitPoleId
				and Pole.DistributionType = 1
			for xml path ('VISIT')
	)		
	return @html

END
/*
select convert(xml, dbo.fn_MapHTML_InspVisitDistributionPole(41424))
 

select *
from tb_Vendor_Distribution_Poles
order by 1 desc
*/